//201902104047 周润恺
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j
public class OpenAreaDao {
    private static OpenAreaDao openAreaDao = new OpenAreaDao();
    private OpenAreaDao(){}
    public static OpenAreaDao getInstance(){
        return openAreaDao;
    }

    /**
     * 
     * 添加课题方向，毕设类型、老师、需求、所受学生人数、年份
     * @param connection
     * @param openArea
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection , OpenArea openArea) throws SQLException {
        //添加课题方向，毕设类型、老师、需求、所受学生人数、年份
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO OpenArea" +
                "(area,graduateProjectCategory_id,teacher_id,requirement,positionNum,year)" +
                "VALUES (?,?,?,?,?,?)");
        //为参数设值
        preparedStatement.setString(1,openArea.getArea());
        preparedStatement.setInt(2,openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(3,openArea.getTeacher().getId());
        preparedStatement.setString(4,openArea.getRequirement());
        preparedStatement.setInt(5,openArea.getPositionNum());
        preparedStatement.setInt(6,openArea.getYear());
        //执行并获得受影响的行数
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        //关闭
        return rowAffected>0;
    }

    /**
     * 获得毕设类型
     * @param connection
     * @param id
     * @return 查找到的毕设类型
     * @throws SQLException
     */
    private GraduateProjectCategory getCategory(Connection connection, int id) throws SQLException {
        //根据id查找
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT  * FROM GraduateProjectCategory WHERE id=?");
        //为id赋值
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //用于储存拿到的数据
        GraduateProjectCategory graduateProjectCategory =null;
        if (resultSet.next()){
            //根据查找到数据新建对象
            graduateProjectCategory = new GraduateProjectCategory(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")
            );
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return graduateProjectCategory;
    }

    /**
     * 根据id查找
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public OpenArea find(Connection connection , int id) throws SQLException {
        OpenArea openArea = null;
        //根据id查找
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM OpenArea WHERE id=?");
        //为id赋值
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //根据查找到数据新建对象
            openArea = new OpenArea(
                    resultSet.getInt("id"),
                    resultSet.getString("area"),
                    this.getCategory(connection,resultSet.getInt("graduateProjectCategory_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    resultSet.getString("requirement"),
                    resultSet.getInt("positionNum"),
                    resultSet.getInt("year")
            );
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return openArea;
    }

    /**
     * 根据条件与分页查询
     * @param connection
     * @param conditions
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<OpenArea> findAll(Connection connection, Collection<Condition> conditions, Pagination pagination) throws SQLException {
        Collection<OpenArea> openAreas = new TreeSet<>();
        //构建查找语句
        StringBuilder select_sql = new StringBuilder("SELECT * FROM OpenArea");
        if (conditions != null){
            //构建条件查询where子句
            //select * from openArea
            select_sql.append(Condition.toWhereClause(JSON.toJSONString(conditions)));
            //select * from openArea where jey op value and key op value...
            log.trace(select_sql.toString());
        }
        if (pagination != null){
            //获得元组总数
            int totalNum = CommonDao.getInstance().count(connection,"OpenArea");
            //构建分页查询limit子句
            select_sql.append(pagination.toLimitClause(totalNum));
            //select * from openArea where jey op value and key op value... limit 3,5;
            log.trace(select_sql.toString());
        }
        PreparedStatement preparedStatement = connection.prepareStatement(select_sql.toString());
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            openAreas.add(
                    new OpenArea(
                            resultSet.getInt("id"),
                            resultSet.getString("area"),
                            this.getCategory(connection,resultSet.getInt("graduateProjectCategory_id")),
                            TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                            resultSet.getString("requirement"),
                            resultSet.getInt("positionNum"),
                            resultSet.getInt("year")
                    )
            );
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return openAreas;
    }

    /**
     * 根据id删除
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delete(Connection connection , int id) throws SQLException {
         PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM OpenArea WHERE id=?");
         preparedStatement.setInt(1,id);
         int rowAffected = preparedStatement.executeUpdate();
         JdbcHelper.close(null,preparedStatement);
         return rowAffected>0;
    }

    /**
     * 更新
     * @param connection
     * @param openArea
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection , OpenArea openArea) throws SQLException {
        PreparedStatement preparedStatement =
                connection.prepareStatement("UPDATE OpenArea " +
                        "SET area=?,graduateProjectCategory_id=?,teacher_id=?,requirement=?,positionNum=?,year=? " +
                        "WHERE id=?");
        //设置更新后的数据
        preparedStatement.setString(1,openArea.getArea());
        preparedStatement.setInt(2,openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(3,openArea.getTeacher().getId());
        preparedStatement.setString(4,openArea.getRequirement());
        preparedStatement.setInt(5,openArea.getPositionNum());
        preparedStatement.setInt(6,openArea.getYear());
        preparedStatement.setInt(7,openArea.getId());
        //执行并获得受影响的行数
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }
}
